package com.example.demo.demos.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.example.demo.demos.domain.dto.UploadDTO;
import com.example.demo.demos.entity.SendMail;
import com.example.demo.demos.entity.UploadList;
import com.example.demo.demos.repository.SendMailRepository;
import com.example.demo.demos.repository.UploadListRepository;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Service
@Slf4j
public class UploadListService {
    @Autowired
    private UploadListRepository uploadListRepository;

    @Autowired
    private EmailService emailService;

    @Autowired
    private SendMailRepository sendMailRepository;

    public void saveInfo(UploadList uploadList)
    {
        uploadListRepository.save(uploadList);
    }

    @Transactional
    public UploadDTO logUpload(String email, String directory, String file) {
        UploadDTO uploadDTO = new UploadDTO();
        uploadDTO.setStatus(1);
        Long beginTime = System.currentTimeMillis();
        StringBuffer msg = new StringBuffer();
        try {
            // 文件上传并比对
            InputStream inputStream = new FileInputStream(file);

            List<List<String>> excelData = ExcelReader.readExcel(inputStream);


            Long excelTime = System.currentTimeMillis();
            msg.append("读取excel时间："+(excelTime-beginTime)+"毫秒;");

            Map<List<String>, Integer> removedRows = new HashMap<>();
            List<List<String>> uniqueData = removeDuplicatesWithStream(excelData, removedRows);

            Map<String, String> removedRowsAnd = new HashMap<>();
            List<List<String>> saveStream = removeDuplicatesWithStreamAnd(excelData,removedRowsAnd);
            log.info(JSON.toJSONString(saveStream));
            Long repeatTime = System.currentTimeMillis();
            msg.append("去重时间："+(repeatTime-excelTime)+"毫秒;");

            // 输出结果
            List<Map<String, Object>> repeatHash = new ArrayList<>();

            if (uniqueData.isEmpty()) {
                uploadDTO.setStatus(0);
                uploadDTO.setMsg("上传数据为空");
            } else {
                List<String> delCols = new ArrayList<>();
                if (!removedRows.isEmpty()) {
                    for (Map.Entry<List<String>, Integer> entry : removedRows.entrySet()) {
                        HashMap hashMap = new HashMap();
                        hashMap.put("line",entry.getKey());
                        hashMap.put("num",entry.getValue());
                        repeatHash.add(hashMap);
                    }
                }
                uploadDTO.setStatus(1);
            }
            List<String> nullCells = IntStream.range(0, uniqueData.size())
                    .boxed()
                    .flatMap(rowIndex -> {
                        List<String> row = uniqueData.get(rowIndex);
                        return IntStream.range(0, row.size())
                                .filter(colIndex -> row.get(colIndex) == null)
                                .mapToObj(colIndex -> "第 " + rowIndex + " 行，第 " + colIndex + " 列为 null");
                    })
                    .collect(Collectors.toList());
            File oldFile = new File(file);

            String newFileName = directory+"\\bak_"+oldFile.getName();
            UploadDTO excelDto = this.writeListToExcel(uniqueData,newFileName);
            if(excelDto.getStatus().equals(0)) {
                throw new RuntimeException("写入excel异常");
            }
            File newFile = new File(excelDto.getMsg());
            StringBuffer stringBuffer = new StringBuffer("邮箱：" + email + " | 目录：" + directory + " | 文件：" + file);
            log.info(stringBuffer.toString());
            // 数据比对，如果有相同文件名的情况
            // 获取文件扩展名
            String fileName = newFile.getName();
            int dotIndex = fileName.lastIndexOf('.');
            String fileExt = (dotIndex > 0) ? fileName.substring(dotIndex + 1) : "";
            UploadList uploadList = new UploadList();
            uploadList.setEmail(email);
            uploadList.setPath(directory);
            Path source = Paths.get(file);
            Path target = Paths.get(directory);
            Path path = Paths.get(directory+"\\"+oldFile.getName());
            if(!Files.exists(path)) {
                Files.copy(source, path, StandardCopyOption.REPLACE_EXISTING);
            }
            File dir = new File(directory+"\\repack");
            if (!dir.exists()) {
                dir.mkdirs(); //
            }
            File originFile = new File(directory+"\\origin");
            if (!originFile.exists()) {
                originFile.mkdirs(); //
            }
            Path targetOrigin = Paths.get(originFile+"\\"+oldFile.getName());
            Files.copy(Paths.get(file), targetOrigin, StandardCopyOption.REPLACE_EXISTING); //复制

            uploadList.setOldFile(directory+"\\"+oldFile.getName()); //历史文件信息
            uploadList.setExt(fileExt);
            uploadList.setSize(String.valueOf(newFile.length()));
            uploadList.setRepeat("");
            uploadList.setNullinfo(JSON.toJSONString(nullCells));
            uploadList.setFileName(newFileName);
            uploadList.setStatus(1);
            Long lastTime = System.currentTimeMillis();
            msg.append("写入文件和数据库时间："+(lastTime - repeatTime)+"毫秒;");
            uploadList.setMsg(msg.toString());
            uploadList.setTime(String.valueOf(beginTime - System.currentTimeMillis()));
            uploadListRepository.save(uploadList);
            uploadListRepository.flush();

            LocalDateTime now = LocalDateTime.now();
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String subject = "文件上传变动信息："+ now.format(formatter);
            String sendStatus = emailService.sendMailWithAttachment(email, subject, subject, newFileName);
            SendMail sendMail = new SendMail();
            String newReName = directory+"\\repack\\"+System.currentTimeMillis()+"_"+oldFile.getName();
            UploadDTO reDTO = this.writeListToExcel(saveStream,newReName);
            if(reDTO.getStatus().equals(0)) {
                throw new RuntimeException("写入excel异常");
            }
            sendMail.setOriginFile(newReName);
            sendMail.setSendStatus(1);
            sendMail.setSendCount(1);
            if (!sendStatus.equals("")) {
                sendMail.setSendStatus(0);
                sendMail.setOriginFile("");
                sendMail.setMsg(sendStatus);
            } else {
                sendMail.setMsg("邮件发送成功");

                sendMail.setUid(uploadList.getId());
                sendMail.setUid(uploadList.getId());
                sendMail.setData("");
                sendMail.setExTime(Integer.valueOf(String.valueOf(beginTime-System.currentTimeMillis())));
                sendMailRepository.save(sendMail);
            }

        } catch (Exception e) {
            uploadDTO.setStatus(0);
            log.info(e.getLocalizedMessage());
            uploadDTO.setMsg(e.getLocalizedMessage());
        }
        log.info(JSON.toJSONString(uploadDTO));
        return uploadDTO;
    }

    /**
     * 使用 Stream API 对 List<List<String>> 去重，并记录被删除的重复行
     */
    public  List<List<String>> removeDuplicatesWithStream(List<List<String>> list, Map<List<String>, Integer> removedRows) {
        Set<List<String>> seen = new HashSet<>();
        return list.stream()
                .filter(Objects::nonNull)
                .filter(row -> {
                    if (seen.contains(row)) {
                        removedRows.put(row, removedRows.getOrDefault(row, 0) + 1);
                        return false;
                    } else {
                        seen.add(row);
                        return true;
                    }
                })
                .collect(Collectors.toList());
    }

    public List<List<String>> removeDuplicatesWithStreamAnd(List<List<String>> list, Map<String, String> removedRows) {
        // 首先统计所有行的出现次数和索引
        Map<String, List<Integer>> rowIndices = new HashMap<>();

        for (int i = 0; i < list.size(); i++) {
            List<String> row = list.get(i);
            if (row == null) {
                continue;
            }

            String rowKey = row.toString();
            rowIndices.computeIfAbsent(rowKey, k -> new ArrayList<>()).add(i);
        }

        List<List<String>> result = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {
            List<String> row = list.get(i);
            if (row == null) {
                continue;
            }

            String rowKey = row.toString();
            List<Integer> indices = rowIndices.get(rowKey);

            List<String> newRow = new ArrayList<>(row);

            // 处理表头
            if (i == 0) {
                newRow.add("是否重复");
                newRow.add("和哪一行重复");
            } else {
                // 检查是否有其他重复行（排除当前行）
                boolean isDuplicate = indices.size() > 1;
                newRow.add(isDuplicate ? "是" : "");

                // 收集除了当前行之外的所有重复行索引
                List<String> otherIndices = new ArrayList<>();
                for (int idx : indices) {
                    if (idx != i) {
                        otherIndices.add(String.valueOf(idx + 1)); // +1 因为行号通常从1开始
                    }
                }

                newRow.add(String.join(",", otherIndices));

                // 如果是重复行，添加到removedRows
                if (isDuplicate) {
                    removedRows.put(rowKey, "Row " + String.join(",", otherIndices));
                }
            }

            result.add(newRow);
        }

        return result;
    }

    /**
     * 写入excel信息
     * @param data
     * @param filePath
     * @return
     */
    public UploadDTO writeListToExcel(List<List<String>> data, String filePath) {
        UploadDTO uploadDTO = new UploadDTO();
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");

            for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
                Row row = sheet.createRow(rowIndex);
                List<String> rowData = data.get(rowIndex);

                for (int colIndex = 0; rowData.size() > colIndex; colIndex++) {
                    Cell cell = row.createCell(colIndex);
                    cell.setCellValue(rowData.get(colIndex));
                }
            }

            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                workbook.write(fos);
            }
            uploadDTO.setStatus(1);
            uploadDTO.setMsg(filePath);
            System.out.println("✅ Excel 文件已生成: " + filePath);
        } catch (IOException e) {
            uploadDTO.setStatus(0);
            uploadDTO.setMsg(e.getMessage());
        }
        return uploadDTO;
    }

    public List<String> getMailArr() {
        List<UploadList> uploadLists = uploadListRepository.findAll();
        List<String> history = uploadLists.stream().map(t->t.getEmail()).distinct().collect(Collectors.toList());
        return history;
    }

    public UploadDTO pollingTest(String email, String directory, String file) {
        UploadDTO uploadDTO = new UploadDTO();
        try {
            Path dirPath = Paths.get(directory); // 替换为实际路径

            if (Files.exists(dirPath) && Files.isDirectory(dirPath)) {
                List<String> fileLists = Files.list(dirPath)
                        .filter(path -> path.toString().toLowerCase().endsWith(".xls") ||
                                path.toString().toLowerCase().endsWith(".xlsx")).map(t->t.toString()).collect(Collectors.toList());
                // 开始轮询
            } else {
                uploadDTO.setStatus(0);
                uploadDTO.setMsg("目录不存在或不是文件夹。");
            }

        } catch (Exception e) {

        }

        return uploadDTO;
    }

    public List<List<String>> removeDuplicatesWithStreamAndOld(List<List<String>> list, Map<String, String> removedRows) {
        Set<String> seen = new HashSet<>();
        List<List<String>> result = new ArrayList<>();

        AtomicInteger index = new AtomicInteger(0);

        list.stream()
                .filter(Objects::nonNull)
                .forEach(row -> {
                    String rowKey = row.toString();

                    if (seen.contains(rowKey)) {
                        // 查找最早出现的索引
                        int firstIndex = -1;
                        List<String> indexData = new ArrayList<>();
                        for (int i = 0; i < index.get(); i++) {
                            if (list.get(i).equals(row)) {
                                indexData.add(String.valueOf(i+1));
//                                break;
                            }
                        }

                        List<String> newRow = new ArrayList<>(row);
                        if (index.get() == 0) {
                            newRow.add("是否重复");
                            newRow.add("和哪一行重复");
                        } else {
                            newRow.add("是");
                            newRow.add("Row " + String.join(",",indexData));
                        }
                        result.add(newRow);

                        removedRows.put(rowKey, "Row " + String.join(",",indexData));
                    } else {
                        seen.add(rowKey);
                        List<String> newRow = new ArrayList<>(row);
                        // 如果是第一行，设置自定义值
                        if (index.get() == 0) {
                            newRow.add("是否重复");
                            newRow.add("和哪一行重复");
                        } else {
                            newRow.add("");
                            newRow.add("");
                        }

                        result.add(newRow);
                    }

                    index.incrementAndGet();
                });

        return result;
    }

}
